package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;

import com.mysql.jdbc.Statement;


import es.ats.codington.festival.domain.Event;
import es.ats.codington.festival.domain.LargeBusiness;
import es.ats.codington.festival.domain.Museum;
import es.ats.codington.festival.domain.Park;
import es.ats.codington.festival.domain.Stadium;
import es.ats.codington.festival.domain.Theater;
import es.ats.codington.festival.domain.TouristAttraction;
import es.ats.codington.festival.domain.TraditionalMarket;
import es.ats.codington.festival.domain.Zoo;
import es.ats.codington.festival.interfaces.daos.IEventDAO;
import es.ats.codington.festival.mappers.EventMapper;
import es.ats.codington.festival.mappers.LargeBusinessMapper;
import es.ats.codington.festival.mappers.MuseumMapper;
import es.ats.codington.festival.mappers.ParkMapper;
import es.ats.codington.festival.mappers.PlaceMapper;
import es.ats.codington.festival.mappers.StadiumMapper;
import es.ats.codington.festival.mappers.TheaterMapper;
import es.ats.codington.festival.mappers.TouristAttractionMapper;
import es.ats.codington.festival.mappers.TraditionalMarketMapper;
import es.ats.codington.festival.mappers.ZooMapper;


/**
 * 
 * EventDAOImpl class
 *
 */
/**
 * @author Transit
 *
 */
public class EventDAOImpl implements IEventDAO {


	// -------------------------------------------
	
	private Connection connection = null;
	private PreparedStatement statement = null;
	private ResultSet resultSet = null;
	
	private JdbcTemplate jdbcTemplateObject;
	private Properties queryProperties;
	
	
	
	
	
	
	/**
	 * Constructor
	 * 
	 * @param connection
	 */
	public EventDAOImpl(Connection connection) {
		this.connection = connection;
	}
		
	/**
	 * Constructor
	 * 
	 * @param jdbcTemplateObject
	 * @param queryProperties
	 */
	
	public EventDAOImpl(JdbcTemplate jdbcTemplateObject,Properties queryProperties ) {
	    this.jdbcTemplateObject = jdbcTemplateObject;
	    this.queryProperties = queryProperties;
	} 
	
	
	// --------------------------Methods ------------------------------
	
	/**
	 * ShowEvents
	 * 
	 * This method returns An ArrayList with all the events.
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public ArrayList<Event> showEvents() throws ClassNotFoundException, SQLException {
				
		ArrayList<Event> eventList = new ArrayList<Event>();
		eventList = (ArrayList<Event>) jdbcTemplateObject.query(queryProperties.getProperty("showEvents"), new EventMapper());
		
				
		return eventList;
		
		}
	
	/////////////////////////////////////////
	
	/**
	 * RegisterNewEvent
	 * 
	 * This method allows the admin for registering a new event
	 * IS NOT IMPLEMENTED IN THIS SPRING VERSION
	 * 
	 * @param event
	 * @return
	 */
	public int registerNewEvent(final Event event)throws ClassNotFoundException, SQLException {
		int affectedRows = 0;	
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("register_event"), 
				new PreparedStatementSetter(){
				
				@Override
				public void setValues(PreparedStatement statement)
						throws SQLException {
                    statement.setString(1, event.getEventName());
                    statement.setString(2, event.getDescription());
                    statement.setInt(3, event.getDuration());
                    statement.setString(4, event.getEventType());
                    statement.setInt(5, event.getSeatsAvailable());
                    statement.setInt(6, event.getSeatsTotal());
                    statement.setString(7, event.getPlace());
                }
            });
		return affectedRows;
		}
	
	///////////////////////////////////////
	
	/**
	 * DeleteEvent
	 * 
	 * This method allows the admin for deleting a new event
	 * IS NOT IMPLEMENTED IN THIS SPRING VERSION
	 * 
	 * @param idEvent
	 * @return
	 */
	public int deleteEvent(int idEvent) throws ClassNotFoundException, SQLException {
		int affectedRows = 0;			 			
		try {
			
			statement = connection.prepareStatement("DELETE from event where idEvent=?");
		    statement.setInt(1, idEvent);
		    
		    affectedRows = statement.executeUpdate();
		    
		} catch(SQLException ex) {
			System.out.println(ex.getMessage());
		}
		return affectedRows;
	}
	
	/**
	 * UpdateEvent
	 * 
	 * * This method allows the admin for updating a new event
	 * IS NOT IMPLEMENTED IN THIS SPRING VERSION
	 * 
	 * @param event
	 * @return
	 */
	public int updateEvent(Event event) throws ClassNotFoundException, SQLException {
		int affectedRows = 0;	
		try {
			
			statement = connection.prepareStatement("UPDATE event set eventName=?, description=?, duration=?,eventType=?,seatsAvailable=?,seatsTotal=?, place=? WHERE idEvent=?");
			statement.setString(1, event.getEventName());
			statement.setString(2, event.getDescription());
			statement.setInt(3, event.getDuration());
			statement.setString(4, event.getEventType());
			statement.setInt(5, event.getSeatsAvailable());
			statement.setInt(6, event.getSeatsTotal());
			statement.setString(7, event.getPlace());
			statement.setInt(8, event.getIdEvent());
			
			affectedRows = statement.executeUpdate();

		} catch(SQLException ex) {
			System.out.println(ex.getMessage());
		}
		return affectedRows;
		}
	
	/**
	 * CheckEventDao
	 *
	 * This method allows the admin for checking if an event exists
	 * IS NOT IMPLEMENTED IN THIS SPRING VERSION
	 * 
	 * 
	 * @param event
	 * @return
	 * @throws SQLException
	 */
	public boolean checkEventDAO(Event event) throws SQLException{
		int row =0;
		try{	
			
			statement = connection.prepareStatement("SELECT * FROM event WHERE eventName=?");
			statement.setString(1, event.getEventName());
			
			resultSet = statement.executeQuery();
			
			resultSet.last();
			row = resultSet.getRow();
			resultSet.beforeFirst();
						
		}catch(Exception eee){
			eee.getMessage();
		}
			
		return row==1;
	}
	
	/**
	 * GetIdEvent
	 * 
	 * returns the id number of a concrete event.
	 * 
	 * @param event
	 * @return
	 * @throws SQLException
	 */
	public int getIdEventDAO(final Event event) throws SQLException{
		
		
		List<Event> events =  jdbcTemplateObject.query(queryProperties.getProperty("getIdEvent"),new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setString(1, event.getEventName());					
			}
		}, new EventMapper());
					
				
		return events.get(0).getIdEvent();
	
	}
	
	
	/**
	 * GetEventDAO
	 * 
	 * return a concrete event searching it by its idNumber
	 * 
	 * @param event
	 * @return
	 * @throws SQLException
	 */
	
	public Event getEventDAO(final Event e) throws SQLException{

		
		List<Event> events =  jdbcTemplateObject.query(queryProperties.getProperty("getEvent"),new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setInt(1, e.getIdEvent());					
			}
		}, new EventMapper());
					
		
		
		return events.get(0);
		
		
		}
	
	
	/**
	 * showEventsByPlaceDAO
	 * 
	 * retuns an Array list with the events of a determinate place
	 * 
	 * @param event
	 * @return
	 * @throws SQLException
	 */
	 
	public ArrayList<Event> showEventsByPlaceDAO(final Event event) throws ClassNotFoundException, SQLException {
				
		ArrayList<Event> eventList = new ArrayList<Event>();
		eventList = (ArrayList<Event>) jdbcTemplateObject.query(queryProperties.getProperty("showEventsByPlace"),new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setString(1, event.getPlace());					
			}
		}, new EventMapper());
		
				
		return eventList;
		
		
		
	}
	
	/**
	 * showStadiumDAO
	 * 
	 * Returns the Stadium Attributes
	 * 
	 * @param event
	 * @return
	 * @throws SQLException
	 */
	
	public Stadium showStadiumDAO(final Stadium stadium) throws ClassNotFoundException, SQLException {
		
		List<Stadium> stadium1 =  jdbcTemplateObject.query(queryProperties.getProperty("showStadium"),new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setInt(1, stadium.getIdPlace());
				statement.setInt(2, stadium.getIdPlace());
				statement.setInt(3, stadium.getIdPlace());					
			}
		}, new StadiumMapper());
		
		
		return stadium1.get(0);
		
		
				
	}
	
	
	/**
	 * showTheaterDAO
	 * 
	 * Returns the theater Attributes
	 * 
	 * @param event
	 * @return
	 * @throws SQLException
	 */
	
public Theater showTheaterDAO(final Theater theater) throws ClassNotFoundException, SQLException {
		
	List<Theater> theater1 =  jdbcTemplateObject.query(queryProperties.getProperty("showTheater"),new PreparedStatementSetter(){
		@Override
		public void setValues(PreparedStatement statement)
				throws SQLException {
			statement.setInt(1, theater.getIdPlace());
			statement.setInt(2, theater.getIdPlace());
			statement.setInt(3, theater.getIdPlace());				
		}
	}, new TheaterMapper());
	
	
	return theater1.get(0);
	
}

/**
 * showMuseumDAO
 * 
 * Returns the Museum Attributes
 * 
 * @param event
 * @return
 * @throws SQLException
 */

public Museum showMuseumDAO(final Museum museum) throws ClassNotFoundException, SQLException {
	
	
	List<Museum> museum1 =  jdbcTemplateObject.query(queryProperties.getProperty("showMuseum"),new PreparedStatementSetter(){
		@Override
		public void setValues(PreparedStatement statement)
				throws SQLException {
			statement.setInt(1, museum.getIdPlace());
			statement.setInt(2, museum.getIdPlace());
			statement.setInt(3, museum.getIdPlace());				
		}
	}, new MuseumMapper());
	
	
	return museum1.get(0);
	
	
	
}


/**
 * showLargeBusinessDAO
 * 
 * Returns the LargeBusiness Attributes
 * 
 * @param event
 * @return
 * @throws SQLException
 */

public LargeBusiness showLargeBusinessDAO(final LargeBusiness largeBusiness) throws ClassNotFoundException, SQLException {
	
	List<LargeBusiness> largebusiness1 =  jdbcTemplateObject.query(queryProperties.getProperty("showLargeBusiness"),new PreparedStatementSetter(){
		@Override
		public void setValues(PreparedStatement statement)
				throws SQLException {
			statement.setInt(1, largeBusiness.getIdPlace());
			statement.setInt(2, largeBusiness.getIdPlace());
			statement.setInt(3, largeBusiness.getIdPlace());				
		}
	}, new LargeBusinessMapper());
	
	
	return largebusiness1.get(0);
	
	
}

/**
 * showZooDAO
 * 
 * Returns the Zoo Attributes
 * 
 * @param event
 * @return
 * @throws SQLException
 */

public Zoo showZooDAO(final Zoo zoo) throws ClassNotFoundException, SQLException {
	
	
	List<Zoo> zoo1 =  jdbcTemplateObject.query(queryProperties.getProperty("showZoo"),new PreparedStatementSetter(){
		@Override
		public void setValues(PreparedStatement statement)
				throws SQLException {
			statement.setInt(1, zoo.getIdPlace());
			statement.setInt(2, zoo.getIdPlace());
			statement.setInt(3, zoo.getIdPlace());				
		}
	}, new ZooMapper());
	
	
	return zoo1.get(0);
	
	
	
}

/**
 * showParkDAO
 * 
 * 
 * Returns the park Attributes
 * 
 * @param event
 * @return
 * @throws SQLException
 */

public Park showParkDAO(final Park park) throws ClassNotFoundException, SQLException {
	
	
	List<Park> park1 =  jdbcTemplateObject.query(queryProperties.getProperty("showPark"),new PreparedStatementSetter(){
		@Override
		public void setValues(PreparedStatement statement)
				throws SQLException {
			statement.setInt(1, park.getIdPlace());
			statement.setInt(2, park.getIdPlace());
			statement.setInt(3, park.getIdPlace());				
		}
	}, new ParkMapper());
	
	
	return park1.get(0);
	
	
	
}

/**
 * showTraditionalMarketDAO
 * 
 * Returns the TraditionalMarket Attributes
 * 
 * @param event
 * @return
 * @throws SQLException
 */

public TraditionalMarket showTraditionalMarketDAO(final TraditionalMarket traditionalMarket) throws ClassNotFoundException, SQLException {
	
	List<TraditionalMarket> traditionalMarket1 =  jdbcTemplateObject.query(queryProperties.getProperty("showTraditionalMarket"),new PreparedStatementSetter(){
		@Override
		public void setValues(PreparedStatement statement)
				throws SQLException {
			statement.setInt(1, traditionalMarket.getIdPlace());
			statement.setInt(2, traditionalMarket.getIdPlace());
			statement.setInt(3, traditionalMarket.getIdPlace());				
		}
	}, new TraditionalMarketMapper());
	
	
	return traditionalMarket1.get(0);
	

	
}

/**
 * showTouristAttractionDAO
 * 
 * Returns the Tourist Attraction Attributes
 * 
 * @param event
 * @return
 * @throws SQLException
 */

public TouristAttraction showTouristAttractionDAO(final TouristAttraction touristAttraction) throws ClassNotFoundException, SQLException {
	
	List<TouristAttraction> touristAttraction1 =  jdbcTemplateObject.query(queryProperties.getProperty("showTouristAttraction"),new PreparedStatementSetter(){
		@Override
		public void setValues(PreparedStatement statement)
				throws SQLException {
			statement.setInt(1, touristAttraction.getIdPlace());
			statement.setInt(2, touristAttraction.getIdPlace());
			statement.setInt(3, touristAttraction.getIdPlace());				
		}
	}, new TouristAttractionMapper());
	
	
	return touristAttraction1.get(0);
	

}

/**
 * getIdPlaceDAO
 * 
 * return the id number of a concrete place searching it by its Placename
 * 
 * @param name
 * @return
 * @throws SQLException
 */

public int getIdPlaceDAO(final String name) throws SQLException{
		
	List<Integer> id =  jdbcTemplateObject.query(queryProperties.getProperty("getIdPlace"),new PreparedStatementSetter(){
		@Override
		public void setValues(PreparedStatement statement)
				throws SQLException {
			statement.setString(1, name);					
		}
	}, new PlaceMapper());
				
	
		
	return id.get(0);

}




	
}
